﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ExcelImport
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            this.btnCancle.Enabled = false;
        }
        Log log = new Log(AppDomain.CurrentDomain.BaseDirectory + @"/log/Log.txt");

        #region 下载模板
        private void linkLabel2_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            string filePath = Application.StartupPath + @"\Excel\卷内目录汇总.xlsx";
            string desdir = SelectPath();
            if (CopyFile(filePath, desdir))
            {
                MessageBox.Show("模板下载成功!");
            }
        }

        private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            string filePath = Application.StartupPath + @"\Excel\案卷级汇总.xlsx";
            string desdir = SelectPath();
            if (CopyFile(filePath, desdir))
            {
                MessageBox.Show("模板下载成功!");
            }
        }

        private string SelectPath() //弹出一个选择目录的对话框
        {
            FolderBrowserDialog path = new FolderBrowserDialog();
            path.ShowDialog();
            return path.SelectedPath;
        }
        
        private bool CopyFile(string srcdir, string desdir)
        {
            string folderName = srcdir.Substring(srcdir.LastIndexOf("\\") + 1);

            string desfolderdir = desdir + "\\" + folderName;

            if (desdir.LastIndexOf("\\") == (desdir.Length - 1))
            {
                desfolderdir = desdir + folderName;
            }
            //File.Exists(desfolderdir);//判断文件是否存在  
            if (File.Exists(desfolderdir))
            {
                MessageBox.Show("请先删除或重命名已存在的Excel（"+folderName+"）！");
                return false;
            }
            else
            {
                File.Copy(srcdir, desfolderdir, true);
                return true;
            }
        }

        #endregion

        #region 网络地址下载模板
        /// <summary>
        /// 下载模板
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnDowns_Click(object sender, EventArgs e)
        {
            string WebPath = ConfigurationManager.AppSettings["WebPath"].ToString();
            string fileName = "案卷级汇总.xlsx"; //文件名
            string filePath = WebPath + fileName; //下载路径
            if (!File.Exists(filePath))
            {
                MessageBox.Show("文件不存在！");
                return;
            }
            Download(filePath, fileName);
        }
        /// <summary>
        /// 下载服务器文件至客户端
        /// </summary>
        /// <param name="URL">被下载的文件地址，绝对路径</param>
        /// <param name="Dir">文件名</param>
        public void Download(string URL, string Dir)
        {
            WebClient client = new WebClient();
            try
            {
                WebRequest myre = WebRequest.Create(URL);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "下载提示");
            }
            try
            {
                //提示用户选择文件在保存位置
                SaveFileDialog sfd = new SaveFileDialog();
                //设置文件类型 
                sfd.Filter = "Excel文件(*.xls,*.xlsx)|*.xls;*.xlsx";
                //设置文件名
                sfd.FileName = Dir;
                //设置默认文件类型显示顺序 
                sfd.FilterIndex = 1;
                //保存对话框是否记忆上次打开的目录 
                sfd.RestoreDirectory = true;

                //点了保存按钮进入 
                if (sfd.ShowDialog() == DialogResult.OK)
                {
                    string localFilePath = sfd.FileName.ToString(); //获得对话框选定在文件路径 
                    client.DownloadFile(URL, localFilePath);//下载文件到本地
                    MessageBox.Show("模板下载成功!");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "下载提示");
            }
        }
        #endregion
        private void txtfile_DoubleClick(object sender, EventArgs e)
        {
            OpenFileDialog dialog = new OpenFileDialog();
            dialog.Multiselect = false;//该值确定是否可以选择多个文件
            dialog.Title = "请选择文件";
            dialog.Filter = "所有文件(*.xlsx)|*.xlsx|所有文件(*.xls)|*.xls";
            if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                string file = dialog.FileName;
                txtfile.Text = file;
            }

        }
        private void btnImport_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(txtfile.Text) || txtfile.Text.Trim() == "双击选择文件")
            {
                MessageBox.Show("请选择文件！");
                return;
            }

            txtbegin.Text = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            txtend.Text = "";
            this.dataGridView1.Rows.Clear();
            if (this.backgroundWorker.IsBusy)
            {
                return;
            }
            this.backgroundWorker.RunWorkerAsync(txtfile.Text);
            this.btnImport.Enabled = false;
            this.btnCancle.Enabled = true;
        }

        private void backgroundWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            try
            {
                e.Result = new DoWork().RetrieveData(this.backgroundWorker, e);
            }
            catch (Exception ce)
            {
                log.log(ce.Message);
                MessageBox.Show(ce.Message);
            }
        }

        private void backgroundWorker_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            try
            {
                Result r = (Result)e.UserState;
                this.progressBar.Value = e.ProgressPercentage;
                this.lblpercent.Text = e.ProgressPercentage.ToString() + "%";
                TimeSpan ts = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")) - Convert.ToDateTime(txtbegin.Text);
                this.txtmin.Text = ts.TotalMinutes.ToString("0.00");
                this.txthour.Text = ts.TotalHours.ToString("0.00");
                this.txtday.Text = ts.TotalDays.ToString("0.00");
                this.lblInfo.Text = "正在读取第" + r.count + "条！";
                int index = this.dataGridView1.Rows.Add();
                this.dataGridView1.Rows[index].Cells[0].Value = r.m.CREATETIME;
                this.dataGridView1.Rows[index].Cells[1].Value = r.m.STATUS;
                this.dataGridView1.Rows[index].Cells[2].Value = r.m.ID;
                this.dataGridView1.Rows[index].Cells[3].Value = r.m.NAME;
                this.dataGridView1.Columns[0].DefaultCellStyle.Format = "yyyy-MM-dd HH:mm:ss";
            }
            catch (Exception ce)
            {
                log.log(ce.Message);
                MessageBox.Show(ce.Message);
            }
        }
        private void backgroundWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            try
            {
                this.btnImport.Enabled = true;

                this.txtend.Text = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                TimeSpan ts = Convert.ToDateTime(txtend.Text) - Convert.ToDateTime(txtbegin.Text);
                this.txtmin.Text = ts.TotalMinutes.ToString("0.00");
                this.txthour.Text = ts.TotalHours.ToString("0.00");
                this.txtday.Text = ts.TotalDays.ToString("0.00");
            }
            catch (Exception ce)
            {
                log.log(ce.Message);
                MessageBox.Show(ce.Message);
            }
        }
        #region 注释
        ///// <summary>
        ///// SQL Server分页
        ///// </summary>
        ///// <param name="originationSql"></param>
        ///// <param name="page"></param>
        ///// <param name="limit"></param>
        ///// <returns></returns>
        //public string PagerStr(string originationSql,int page,int limit)
        //{
        //    string sqlString = string.Empty;

        //        //p2.Add("CurrentPageIndex", Index);//注意：分页参数
        //        //p2.Add("PageSize", Size);//注意：分页参数

        //        sqlString = string.Format(@"declare @CurrentPageIndex int={2}
        //                                    declare @PageSize int={3}
        //                                    ;with pagnation as 
        //                                    (
        //                                    SELECT Row_Number() OVER(ORDER BY tbl_Data.{1}) as ROW_NUM, tbl_Data.* FROM (
        //                                    --SQL语句--start--
        //                                    {0}
        //                                    --SQL语句--end--
        //                                    ) AS tbl_Data
        //                                    ),T2 as
        //                                    (select top 1 with ties ROW_NUM from pagnation order by ROW_NUM desc)  --记录数
        //                                    select T2.ROW_NUM TOTAL_NUM, T1.* from pagnation as T1,T2 
        //                                    where T1.ROW_NUM
        //                                    between (@CurrentPageIndex-1)*@PageSize+1 and @CurrentPageIndex*@PageSize", originationSql , "CREATETIME asc", page , limit);

        //    return sqlString;
        //}
        //int page = 1;
        //string sqlsel = @"SELECT CREATETIME 
        //                  ,CASE STATUS WHEN 1 THEN '成功' ELSE '失败' END AS 'STATUS'
        //                  ,ID
        //                  ,NAME
        //                  ,ADDRESS
        //                  ,EMAIL
        //                  ,TEL
        //                  ,MOBILE
        //                FROM FileLog
        //                WHERE CREATETIME>='{0}'";
        //public string PagerStrForMySql(string originationSql, int page, int limit)
        //{
        //    return originationSql + " LIMIT " + (page-1)*limit + "," + limit;
        //}
        //private void btnHome_Click(object sender, EventArgs e)
        //{
        //    page = 1;
        //    btnPre.Enabled = false;
        //    string sql = PagerStrForMySql(string.Format(sqlsel,txtbegin.Text),page,10);
        //    SQLHelper sqlhelper = new SQLHelper();
        //    DataTable dt1 = sqlhelper.FindDataTable(sql,null);
        //    dataGridView1.DataSource = dt1;
        //}

        //private void btnNext_Click(object sender, EventArgs e)
        //{
        //    page++;
        //    btnPre.Enabled = true;
        //    string sql = PagerStrForMySql(string.Format(sqlsel, txtbegin.Text), page, 10);
        //    SQLHelper sqlhelper = new SQLHelper();
        //    DataTable dt1 = sqlhelper.FindDataTable(sql, null);
        //    dataGridView1.DataSource = dt1;

        //}

        //private void btnPre_Click(object sender, EventArgs e)
        //{
        //    page--;
        //    if (page == 1)
        //    {
        //        btnPre.Enabled = false;
        //    }
        //    string sql = PagerStrForMySql(string.Format(sqlsel, txtbegin.Text), page, 10);
        //    SQLHelper sqlhelper = new SQLHelper();
        //    DataTable dt1 = sqlhelper.FindDataTable(sql, null);
        //    dataGridView1.DataSource = dt1;
        //}
        #endregion
        private void btnCancle_Click(object sender, EventArgs e)
        {
            try
            {
                if (this.backgroundWorker.IsBusy == true)
                {
                    //进入这里后 还是 会触发 backgroundWorker_RunWorkerCompleted 事件的
                    this.backgroundWorker.CancelAsync();
                }
                else
                {
                    this.txtend.Text = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                    TimeSpan ts = Convert.ToDateTime(txtend.Text) - Convert.ToDateTime(txtbegin.Text);
                    this.txtmin.Text = ts.TotalMinutes.ToString("0.00");
                    this.txthour.Text = ts.TotalHours.ToString("0.00");
                    this.txtday.Text = ts.TotalDays.ToString("0.00");

                    this.btnImport.Enabled = true;
                }
            }
            catch (Exception ce)
            {
                log.log(ce.Message);
                MessageBox.Show(ce.Message);
            }
        }

        private void btnReset_Click(object sender, EventArgs e)
        {
            try
            {
                this.txtbegin.Text = "";
                this.txtend.Text = "";
                this.txtfile.Text = "    双击选择文件";
                this.lblInfo.Text = "未开始！";
                this.dataGridView1.Rows.Clear();
                this.lblpercent.Text = "0%";
                this.progressBar.Value = 0;
                this.btnCancle.Enabled = false;
                this.txtmin.Text = "";
                this.txthour.Text = "";
                this.txtday.Text = "";
            }
            catch (Exception ce)
            {
                log.log(ce.Message);
                MessageBox.Show(ce.Message);
            }
        }

    }
}
